package com.juli.aimanager.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.juli.aimanager.util.SqlParser;

@Repository
public class BaseDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	public List<Map<String,Object>> findAll(String sql) throws Exception{
		return findAll( sql,new HashMap());
		
	}
	
	public List<Map<String,Object>> findAll(String sql,Map<String,String> paramsMap) throws Exception{
		paramsMap=SqlParser.escape4select(paramsMap);
		sql = SqlParser.parse(sql, paramsMap);
		
		
		return this.jdbcTemplate.queryForList(sql);
		
	}
	
	public Map<String,Object> findMap(String sql) throws Exception{
		
		return findMap( sql,new HashMap());
		
	}
	
	public Map<String,Object> findMap(String sql,Map<String,String> paramsMap) throws Exception{
		paramsMap=SqlParser.escape4select(paramsMap);
		sql = SqlParser.parse(sql, paramsMap);
		
		
		return this.jdbcTemplate.queryForMap(sql);
		
	}
	public Map queryGridList(String sql, Map paramsMap) throws Exception {

		paramsMap = SqlParser.escape4select(paramsMap);
		sql = SqlParser.parse(sql, paramsMap);
		System.out.println("----------------查询sql--------" + sql);
		String coutSql = "select count(1)  from (" + sql + ") ";
		String gridSql = oracleGetSql(sql,
				Integer.parseInt(paramsMap.get("page").toString()),
				Integer.parseInt(paramsMap.get("rows").toString()));
		int count = this.queryForInt(coutSql);
		Map gridMap = new HashMap();
		gridMap.put("total", count);
		gridMap.put("rows", jdbcTemplate.queryForList(gridSql));

		return gridMap;
	}
	/**
	 * mysql
	 * @param sql
	 * @param paramsMap
	 * @return
	 * @throws Exception
	 */
	public Map queryGridListMySql(String sql, Map paramsMap) throws Exception {
		paramsMap = SqlParser.escape4select(paramsMap);
		sql = SqlParser.parse(sql, paramsMap);
		System.out.println("----------------查询sql--------" + sql);
		String coutSql = "select count(1)  from (" + sql + ") ccount";
		String gridSql = mysqlGetSql(sql,
				Integer.parseInt(paramsMap.get("page").toString()),
				Integer.parseInt(paramsMap.get("rows").toString()));
		int count = this.queryForInt(coutSql);
		Map gridMap = new HashMap();
		gridMap.put("total", count);
		gridMap.put("rows", jdbcTemplate.queryForList(gridSql));

		return gridMap;
	}
	/**
	 * oracle
	 * @param oldSqlStr
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public String oracleGetSql(String oldSqlStr, int pageNo, int pageSize){
		int low =  (pageNo - 1) * pageSize + 1;
		int up = pageNo * pageSize;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM(SELECT A.*, rownum as rid FROM( ");
		sb.append(oldSqlStr);
		sb.append((new StringBuilder()).append(") A WHERE rownum<=").append(up).append(") WHERE rid >=").append(low).toString());
		return sb.toString();
	}
	/**
	 * mysql
	 * @param oldSqlStr
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public String mysqlGetSql(String oldSqlStr, int pageNo, int pageSize){
		int low = pageNo ;
		int up = pageSize;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM(");
		sb.append(oldSqlStr);
		sb.append((new StringBuilder()).append(") A WHERE 1=1 limit ").append(low).append(",").append(up));
		return sb.toString();
	}
	
	
	public int queryForInt(String sql, Map paramsMap) throws Exception {
		if(paramsMap!=null){
			paramsMap = SqlParser.escape4select(paramsMap);
			sql = SqlParser.parse(sql, paramsMap);
		}
		sql = SqlParser.parse(sql, paramsMap);
		return this.queryForInt(sql) ;
	}
	
	public int queryForInt(String sql)throws Exception{
		return jdbcTemplate.queryForObject(sql, Integer.class) ;
	}
	public List<Long> queryListRoleId(String sql) throws Exception{
		return jdbcTemplate.queryForObject(sql, List.class);
	}
	public String queryForString(String sql){
		return jdbcTemplate.queryForObject(sql, String.class);
	}
	
	public Object findOneObject(String sql,Class clz) throws Exception{
		List list=this.findAllObject(sql, clz);
		Object obj=null;
		if(list.size()==1){
			 obj=list.get(0);
		}
		return obj;
	}
	
	public List findAllObject(String sql,Class clz)throws Exception{
		return this.findAllObject(sql, new HashMap(), clz);
	}
	
	public List findAllObject(String sql,Map paramsMap ,Class classObj) throws Exception {
		// TODO Auto-generated method stub
		if(paramsMap!=null){
			paramsMap = SqlParser.escape4select(paramsMap);
			sql = SqlParser.parse(sql, paramsMap);
		}
		sql = SqlParser.parse(sql, paramsMap);
		List list=jdbcTemplate.query(sql,new BeanPropertyRowMapper(classObj));
		return list;
		
	}
	
	public void addObject(String sql){
		
		jdbcTemplate.execute(sql);
	}

}
